--exec sp_get_all_products_for_migration
ALTER   PROC sp_get_all_products_for_migration as 
BEGIN
	DECLARE @results TABLE( part_Number VARCHAR(50), ps_Type VARCHAR(50), ps_type_name VARCHAR(50), p_comments TEXT, gp_vendor_id INT, p_type VARCHAR(50), partGenDesc VARCHAR(11) ) 

	-- FETCH ALL CHIPS
	INSERT @results SELECT PART_NUMBER, PS_TYPE, PS_TYPE_NAME, P_COMMENTS, GP_VENDOR_ID, 'CHIP', 'CHIP' AS P_TYPE FROM PRODUCT, PROCESS_STEP, CT_GP_VENDOR_MAPPING, Process_Step_Location_Table, Location WHERE PRODUCT.ProcessStep_ID = PROCESS_STEP.ProcessStep_ID and Process_Step_Location_Table.ProcessStep_ID = PRODUCT.ProcessStep_ID and Process_Step_Location_Table.Location_ID = Location.Location_ID and Location.Vendor_ID = CT_GP_VENDOR_MAPPING.CT_Vendor_ID and P_TYPE is null
	
	-- FETCH ALL MOTHER BOARDS AND KITS
	INSERT @results SELECT PART_NUMBER, PS_TYPE, PS_TYPE_NAME, P_COMMENTS, GP_VENDOR_ID, P_TYPE, SUBSTRING( P_TYPE, 1, 11 ) FROM PRODUCT, PROCESS_STEP, CT_GP_VENDOR_MAPPING, Process_Step_Location_Table, Location WHERE PRODUCT.ProcessStep_ID = PROCESS_STEP.ProcessStep_ID and Process_Step_Location_Table.ProcessStep_ID = PRODUCT.ProcessStep_ID and Process_Step_Location_Table.Location_ID = Location.Location_ID and Location.Vendor_ID = CT_GP_VENDOR_MAPPING.CT_Vendor_ID and P_TYPE is not null and P_Parent_Product_Id = -1

	-- FETCH ALL THE CHILD BOARDS AND KITS
	INSERT @results SELECT PART_NUMBER, PS_TYPE, PS_TYPE_NAME, P_COMMENTS, GP_VENDOR_ID, P_TYPE, SUBSTRING( P_TYPE+' CHILD', 1, 11 ) FROM PRODUCT P, PROCESS_STEP, CT_GP_VENDOR_MAPPING, Process_Step_Location_Table, Location WHERE P.ProcessStep_ID = PROCESS_STEP.ProcessStep_ID and Process_Step_Location_Table.ProcessStep_ID = (SELECT prod.processStep_Id from Product prod where prod.Product_id = P.P_Parent_Product_Id  ) and Process_Step_Location_Table.Location_ID = Location.Location_ID and Location.Vendor_ID = CT_GP_VENDOR_MAPPING.CT_Vendor_ID and P_TYPE is not null and P_Parent_Product_Id <> -1

	SELECT * FROM @results
END
